Find Empty Excel Cell

Mathias Mamsch - Tue Aug 21 10:49:04 EDT 2012

Try this:
 

int findEmptyRow (OleAutoObj objExcelSheet)
{
    OleAutoObj objUsedRange, objRows
        int iRow = 0
        oleGet(objExcelSheet,"UsedRange",objUsedRange)
        oleGet(objExcelSheet,"Rows",objRows)
        oleGet(objExcelSheet,"Count",iRow)
        return iRow+1
}

Did not try it, but it is supposed to find the number of rows that are used in the Worksheet (I guess this will assume that your first row is part of the UsedRange). Maybe that helps, regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

josiah820 - Tue Aug 21 11:23:29 EDT 2012
I'm sure that works, but for some reason it's not modifying iRow. I printed "iRow + 1" multiple times and it continued to print "1", until I changed iRow to 5, after which it printed "6". So, how would I make this modify iRow?
Thanks

Mathias Mamsch - Tue Aug 21 17:18:05 EDT 2012

This is a function which will not affect any global variable. It declares its own iRow variable. Instead it will return the new iRow, so you need to do:
 

iRow = findNext...(...)

Regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

SystemAdmin - Wed Aug 22 07:47:32 EDT 2012

I think it should be this:

int findEmptyRow (OleAutoObj objExcelSheet)
{
    OleAutoObj objUsedRange, objRows
        int iRow = 0
        oleGet(objExcelSheet,"UsedRange",objUsedRange)
        oleGet(objUsedRange,"Rows",objRows)
        oleGet(objRows,"Count",iRow)
        return iRow+1
}

Anyway, you should not rely on the UsedRange property. It has problems:
It is only updated when the workbook is opened,
It counts formatted cells as used even it they are empty
probably others which I forget now (google it)

Probably best to look up the vba code on a vba help forum and come back here if you need help converting it to dxl.
That way you will learn the vba issues.

llandale - Wed Aug 22 10:10:16 EDT 2012
Forgive ignorance, but do you also not need:

• delete(objUsedRange)
• delete(objRows)

josiah820 - Wed Aug 22 10:23:27 EDT 2012
so I should delete these lines:

oleGet(objExcelSheet,"UsedRange",objUsedRange)
oleGet(objExcelSheet,"Rows",objRows)

??
Thanks

llandale - Wed Aug 22 12:41:21 EDT 2012

No, I'm thinking this:

int findEmptyRow (OleAutoObj objExcelSheet)
{
    OleAutoObj objUsedRange, objRows
        int iRow = 0
        oleGet(objExcelSheet,"UsedRange",objUsedRange)
        oleGet(objUsedRange,"Rows",objRows)
        oleGet(objRows,"Count",iRow)
 
        delete(objUsedRange)  // <<Inserted
        delete(objRows)       // <<Inserted
 
        return iRow+1
}

llandale - Wed Aug 22 12:41:21 EDT 2012

No, I'm thinking this:

int findEmptyRow (OleAutoObj objExcelSheet)
{
    OleAutoObj objUsedRange, objRows
        int iRow = 0
        oleGet(objExcelSheet,"UsedRange",objUsedRange)
        oleGet(objUsedRange,"Rows",objRows)
        oleGet(objRows,"Count",iRow)
 
        delete(objUsedRange)  // <<Inserted
        delete(objRows)       // <<Inserted
 
        return iRow+1
}

josiah820 - Wed Aug 22 13:01:44 EDT 2012
Okay, gotcha! thanks! can you think of any reason why I'd be getting the unassigned variable error on line 128?? What should I use as the objExcelSheet? I only have two days left in my internship here, and really need to finish this before it's up. I feel like that's all I have left to figure out, and it's honestly stumping me...
Thanks!!

llandale - Wed Aug 22 13:28:18 EDT 2012
I see that global variable "objExcelSheet" is only set in function "excelGetWorksheet()" which is never called. Thus "objExcelSheet" is not defined. If you declared it "OleAutoObj objExcelSheet = null" then you'd get some sort of "null parameter" error instead.

Others:

• I see that function findEmptyRow() uses call-parameter spelled exactly the same "objExcelSheet", which may cause some confusion.
• I see that you call function findEmptyRow(ex) from function openExcel(), but "ex" is the application handle not the sheet handle.
• Function pasteInt() seems to be missing "copyToClipboard t".
• Functions "deleteFilter" "filterer" and "filter()" need "current = currentModule" at the top. Lets make sure you know which module is current before using perms that presume the current module.
• You have nice well-defined functions, but these can be improved by eschewing global variables and returning the result directly.
  • e.g. turn this:
    • void excelGetWorksheet(OleAutoObj oleExcel, int iSheetNumber)
  • into this:
    • OleAutoObj excelGetWorksheet(OleAutoObj oleExcel, int iSheetNumber)
    • {..
    • return(oSheet)
    • }
  • Let the main "caller" set the global; or better yet just pass it around to functions that need it.

llandale - Wed Aug 22 13:28:18 EDT 2012
I see that global variable "objExcelSheet" is only set in function "excelGetWorksheet()" which is never called. Thus "objExcelSheet" is not defined. If you declared it "OleAutoObj objExcelSheet = null" then you'd get some sort of "null parameter" error instead.

Others:

• I see that function findEmptyRow() uses call-parameter spelled exactly the same "objExcelSheet", which may cause some confusion.
• I see that you call function findEmptyRow(ex) from function openExcel(), but "ex" is the application handle not the sheet handle.
• Function pasteInt() seems to be missing "copyToClipboard t".
• Functions "deleteFilter" "filterer" and "filter()" need "current = currentModule" at the top. Lets make sure you know which module is current before using perms that presume the current module.
• You have nice well-defined functions, but these can be improved by eschewing global variables and returning the result directly.
  • e.g. turn this:
    • void excelGetWorksheet(OleAutoObj oleExcel, int iSheetNumber)
  • into this:
    • OleAutoObj excelGetWorksheet(OleAutoObj oleExcel, int iSheetNumber)
    • {..
    • return(oSheet)
    • }
  • Let the main "caller" set the global; or better yet just pass it around to functions that need it.

josiah820 - Wed Aug 22 13:51:19 EDT 2012
iRow still isn't being changed, though, even though I set it through running int findEmptyRow(). Here's my current code

josiah820 - Wed Aug 22 13:51:19 EDT 2012
iRow still isn't being changed, though, even though I set it through running int findEmptyRow(). Here's my current code

llandale - Wed Aug 22 14:29:22 EDT 2012
In this code, global "iRow" isn't used at all. I see p = findEmptyRow() in displayResults().

josiah820 - Wed Aug 22 13:04:49 EDT 2012
and I'm getting an incorrect arguments for function (delete) with that code, so I'm guessing it shouldn't be in there...

josiah820 - Wed Aug 22 14:31:09 EDT 2012
I meant "iRows" in the findEmptyRow() method

josiah820 - Wed Aug 22 14:30:00 EDT 2012
UPDATE:
This program works completely, except for the findEmptyRow() method, in which the variable iRows is not being changed. Any idea what could be up?
Thanks

Mathias Mamsch - Thu Aug 23 03:43:01 EDT 2012

Sorry I posted that findLastRow Message from some post on the internet without trying it. Adamarla is right, UsedRange is no way to go. A fairly slow but working example is here:
 

OleAutoObj wbs 
OleAutoObj wb
OleAutoObj ws
OleAutoObj objCell
OleAutoArgs args = create
 
void openExcel() {
   OleAutoObj ex = oleCreateAutoObject "Excel.Application"
   oleGet (ex, "Workbooks", wbs) 
   oleMethod (wbs, "Add", args, wb)
   olePut(ex, "Visible", true)
   put(args, 1)
   oleGet (ex, "Worksheets", args, ws) 
} 
 
void pasteString (int y, int x, string s) {
                clear args
                        put (args, y)
                        put (args, x)
                        oleGet (ws, "Cells", args, objCell) // select cell
 
                        clear args
                        put (args, objCell)
                        copyToClipboard s
                        print oleMethod (ws, "Paste",args)
}
 
// VBA code taken from here: http://www.dbforums.com/microsoft-excel/1654111-find-next-empty-row.html
int findEmptyRow (OleAutoObj objExcelSheet)
{
        OleAutoObj objRange, objRow
        OleAutoArgs args = create() 
        oleGet(objExcelSheet,"Cells",args, objRange)
      int iRow = 0
      clear args
      put (args,  "What", "*")
      put (args, "SearchOrder", 1) // xlByRows
      put (args, "SearchDirection", 2) // xlPrevious
        oleMethod(objRange,"Find",args, objRow)
      if (null objRow) oleGet(objRange, "Row", iRow) 
      else (oleGet(objRow,"Row",iRow))
      delete args
      oleCloseAutoObject objRange
      oleCloseAutoObject objRow
      return iRow+1
}
 
openExcel() 
 
int i; for (i = 0; i < 10; i++) {
   int iNext = findEmptyRow(ws)
   pasteString (iNext, 1, "Hello") 
 
   iNext = findEmptyRow(ws)
   pasteString (iNext, 2, "World!") 
}

Hope this helps, regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS